package AdminController.AdminExe;
import Connect.connector;
import PhotoController.PhotoOldBean;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.sql.*;

public class AdminExeDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public AdminExeBean[] getList(int number, int size){
		conn = connector.getConnection();
		ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		
		try{
			
			int startnum;
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from ADMINOLD ORDER BY `ID` DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1,startnum);
			pstmt.setInt(2,size);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminExeBean aeBean = new AdminExeBean();
				
				aeBean.setID(rs.getInt("ID"));
				aeBean.setMem_Id(rs.getInt("Mem_Id"));
				aeBean.setMem_Nick(rs.getString("Mem_Nick"));
				result.add(aeBean);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminExeBean[0]);
	}
	
	public int memberCount(){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select count(*) from ADMINOLD";
			
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt("count(*)");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	
	public int finalCount(){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select count(*) from ADMINCHECKY";
			
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt("count(*)");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	
	
	public int memberCountY(){
		conn = connector.getConnection();
		
		try{
			
			String sql = "select count(*) from BABYPLUS";
			
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();
			
			if( rs.next()){
				
				int count = rs.getInt("count(*)");
				return count;
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return 0;
	}
	
	public AdminExeBean getCheckList(AdminExeBean aeBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct a.ID,a.Mem_NameK,a.Mem_NameH,a.Mem_Year,a.Mem_Month,a.Mem_Day,a.Mem_Mobile,b.Members_Id,b.Old_Add,b.Old_Kind,b.Old_Gender,b.Old_Money,b.Old_Photo,a.Mem_Email,c.Vvalue,d.Dvalue,a.Mem_Nick" );
			sql.append(	" from MEMBERS a,OLDPLUS b,ABLILTY c,DAYCHECK d " );				
			sql.append( " where c.Members_Id = b.Members_Id and b.Members_Id = d.Members_Id and a.ID = d.Members_Id and a.ID=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				aeBean.setID(rs.getInt(1));
				aeBean.setMem_NameK(rs.getString(2));
				aeBean.setMem_NameH(rs.getString(3));
				aeBean.setMem_Year(rs.getString(4));
				aeBean.setMem_Month(rs.getString(5));
				aeBean.setMem_Day(rs.getString(6));
				aeBean.setMem_Mobile(rs.getString(7));
				aeBean.setMembers_Id(rs.getInt(8));
				aeBean.setOld_Add(rs.getString(9));
				aeBean.setOld_Kind(rs.getString(10));
				aeBean.setOld_Gender(rs.getInt(11));
				aeBean.setOld_Money(rs.getString(12));
				aeBean.setOld_Photo(rs.getString(13));
				aeBean.setMem_Email(rs.getString(14));
				aeBean.setVvalue(rs.getString(15));
				aeBean.setDvalue(rs.getString(16));
				aeBean.setMem_Nick(rs.getString(17));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeBean;
	}
	
	public String getDvalueList(AdminExeBean aeBean){
		conn = connector.getConnection();
		//ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		String num = "";
		try{
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select Dvalue" );
			sql.append(	" from DAYCHECK  " );				
			sql.append( " where Members_Id=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			
			
			while(rs.next()){
				
				num += rs.getString(1)+",";
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return num;
	}
	
	
	
public String getDvalueYList(AdminExeBean aeyBean){
		conn = connector.getConnection();
		//ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		String num = "";
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select Dvalue" );
			sql.append(	" from DAYCHECK  " );				
			sql.append( " where Members_Id=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeyBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			
			
			while(rs.next()){
				
				num += rs.getString(1)+",";
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return num;
	}
public String getVvalueList(AdminExeBean aeBean){
	conn = connector.getConnection();
	//ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
	String num = "";
	try{
		
		StringBuffer sql = new StringBuffer();
		
		sql.append( " select Vvalue" );
		sql.append(	" from ABLILTY  " );				
		sql.append( " where Members_Id=?" );
		
		String sql1 = sql.toString();
		
		pstmt = conn.prepareStatement( sql1 );
		
		pstmt.setInt(1,aeBean.getMem_Id());
		
		rs = pstmt.executeQuery();
		
		
		
		while(rs.next()){
			
			num += rs.getString(1)+",";
			
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		connector.releaseConnection(conn, pstmt, rs);
	}
	return num;
}
	public String getVvalueYList(AdminExeBean aeyBean){
	conn = connector.getConnection();
	//ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
	String num = "";
	try{
		
		StringBuffer sql = new StringBuffer();
		
		sql.append( " select Vvalue" );
		sql.append(	" from ABLILTY  " );				
		sql.append( " where Members_Id=?" );
		
		String sql1 = sql.toString();
		
		pstmt = conn.prepareStatement( sql1 );
		
		pstmt.setInt(1,aeyBean.getMem_Id());
		
		rs = pstmt.executeQuery();
		
		
		
		while(rs.next()){
			
			num += rs.getString(1)+",";
			
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		connector.releaseConnection(conn, pstmt, rs);
	}
	return num;
}

	public String checkCid(AdminExeBean aeyBean){
		conn = connector.getConnection();
		//ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		String id = "";
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select ID" );
			sql.append(	" from BABYPHOTO" );				
			sql.append( " where Bbp_Members=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeyBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
		while(rs.next()){
				
				id += rs.getString(1)+",";
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return id;
	}

	public void aceptDel(int id){
			
		conn = connector.getConnection();
		try{
				
				System.out.println("삭제할 번호:"+id);
				String sql = "delete from ADMINOLD where Mem_Id=?";
				
				pstmt =  conn.prepareStatement( sql );
				pstmt.setInt(1,id);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
	
	public void checkupO(int id,int up){
		conn = connector.getConnection();
		try{
			String sql = "update OLDPLUS set Old_Check=? where Members_Id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,up);
			pstmt.setInt(2,id);
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt,rs);
		}
	}
	
	public void finalupO(int id,int up){
		conn = connector.getConnection();
		try{
			String sql = "update ADMINCHECKO set Mem_Check=? where Mem_Id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,up);
			pstmt.setInt(2,id);
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt,rs);
		}
	}
	
	public void finalAceptO(int id,String nick){
		
		conn = connector.getConnection();
		try{
				
				System.out.println("접수성공번호:"+id);
				String sql = "insert into ADMINCHECKO (ID,Mem_Nick,Mem_Id,Mem_Check) values(null,?,?,1)";
				
				pstmt =  conn.prepareStatement( sql );
				pstmt.setString(1,nick);
				pstmt.setInt(2,id);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
	public void finalAceptY(int id,String nick){
		
		conn = connector.getConnection();
		try{
				
				System.out.println("접수성공번호:"+id);
				String sql = "insert into ADMINCHECKY (ID,Mem_Nick,Mem_Id,Mem_Check,Map_Check) values(null,?,?,1,3)";
				
				pstmt =  conn.prepareStatement( sql );
				pstmt.setString(1,nick);
				pstmt.setInt(2,id);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
	
	public String checkNick(int id){
		conn  = connector.getConnection();
		String Mem_Nick="";
		try{
			
			String sql = "select Mem_Nick from MEMBERS where ID=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,id);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				Mem_Nick = rs.getString(1);
			}
		  
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	  return Mem_Nick;
	}
	
	
	public void aceptDelY(int id){
		
		conn = connector.getConnection();
		try{
				
				System.out.println("삭제할 번호:"+id);
				String sql = "delete from ADMINYOUNG where Mem_Id=?";
				
				pstmt =  conn.prepareStatement( sql );
				pstmt.setInt(1,id);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
		}
	
	public void checkupY(int id,int up){
		conn = connector.getConnection();
		try{
			String sql = "update BABYPLUS set Bby_Check=? where Members_Id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,up);
			pstmt.setInt(2,id);
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt,rs);
		}
	}
	
	public void finalupY(int id,int up,int mFup){
		conn = connector.getConnection();
		try{
			String sql = "update ADMINCHECKY set Mem_Check=? ,Map_Check=? where Mem_Id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,up);
			pstmt.setInt(2,mFup);
			pstmt.setInt(3,id);
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt,rs);
		}
	}
	
	public AdminExeBean[] getYList(int number,int size){
		conn = connector.getConnection();
		ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		
		try{
			int startnum;
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			
			String sql = "select * from (select * from ADMINYOUNG ORDER BY `ID` DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1,startnum);
			pstmt.setInt(2,size);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminExeBean aeBean = new AdminExeBean();
				
				aeBean.setID(rs.getInt("ID"));
				aeBean.setMem_Id(rs.getInt("Mem_Id"));
				aeBean.setMem_Nick(rs.getString("Mem_Nick"));
				result.add(aeBean);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminExeBean[0]);
	}
	
	public AdminExeBean getCheckYList(AdminExeBean aeyBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			System.out.println("몇번?"+aeyBean.getMem_Id());
			sql.append( " select distinct a.ID,a.Mem_NameK,a.Mem_NameH,a.Mem_Year,a.Mem_Month,a.Mem_Day,a.Mem_Mobile,b.Members_Id,b.Bby_Add,b.Bby_Kind,b.Bby_Gender,b.Bby_Photo,b.Bby_Contents,a.Mem_Nick,b.Bby_Money" );
			sql.append(	" from MEMBERS a,BABYPLUS b,BABYPHOTO c " );				
			sql.append( " where a.ID = b.Members_Id and  a.ID=? " );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeyBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				aeyBean.setID(rs.getInt(1));
				aeyBean.setMem_NameK(rs.getString(2));
				aeyBean.setMem_NameH(rs.getString(3));
				aeyBean.setMem_Year(rs.getString(4));
				aeyBean.setMem_Month(rs.getString(5));
				aeyBean.setMem_Day(rs.getString(6));
				aeyBean.setMem_Mobile(rs.getString(7));
				aeyBean.setMembers_Id(rs.getInt(8));
				aeyBean.setBby_Add(rs.getString(9));
				aeyBean.setBby_Kind(rs.getString(10));
				aeyBean.setBby_Gender(rs.getInt(11));
				aeyBean.setBby_Photo(rs.getString(12));
				aeyBean.setBby_Contents(rs.getString(13));
				aeyBean.setMem_Nick(rs.getString(14));
				aeyBean.setBby_Money(rs.getString(15));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeyBean;
	}

	public AdminExeBean getChildList(AdminExeBean aeyBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			System.out.println("몇번?"+aeyBean.getMem_Id());
			sql.append( " select ID,Babyplus_Id, Bbp_NameK, Bbp_NameH, Bbp_Birth, Bbp_Gender, Bbp_Photo" );
			sql.append(	" from BABYPHOTO " );				
			sql.append( " where ID = ? " );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeyBean.getCID());
			
			rs = pstmt.executeQuery();

			SimpleDateFormat check = new SimpleDateFormat("yyyy-MM-dd");
			
			while(rs.next()){
				
				aeyBean.setID(rs.getInt(1));
				aeyBean.setBabyplus_Id(rs.getInt(2));
				aeyBean.setBbp_Namek(rs.getString(3));
				aeyBean.setBbp_NameH(rs.getString(4));
				aeyBean.setBbp_Birth(check.format(rs.getTimestamp(5)));
				aeyBean.setBbp_Gender(rs.getInt(6));
				aeyBean.setBbp_Photo(rs.getString(7));
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeyBean;
	}

	public AdminExeBean[] finalList(int number,int size){
		conn = connector.getConnection();
		ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		
		int startnum;
	
		try{
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from ADMINCHECKY where Mem_Check=1 ORDER BY `ID` DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1,startnum);
			pstmt.setInt(2,size);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminExeBean aeBean = new AdminExeBean();
				
				aeBean.setID(rs.getInt("ID"));
				aeBean.setMem_Nick(rs.getString("Mem_Nick"));
				aeBean.setMem_Id(rs.getInt("Mem_Id"));
				result.add(aeBean);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminExeBean[0]);
	}
	
	public AdminExeBean[] RefinalList(int number,int size){
		conn = connector.getConnection();
		ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		
		int startnum;
		
		try{
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from ADMINCHECKY where Mem_Check=2 or Mem_Check=5 ORDER BY `ID` DESC) a where a.ID limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1,startnum);
			pstmt.setInt(2,size);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				AdminExeBean aeBean = new AdminExeBean();
				
				aeBean.setID(rs.getInt("ID"));
				aeBean.setRmem_Nick(rs.getString("Mem_Nick"));
				aeBean.setRmem_Id(rs.getInt("Mem_Id"));
				aeBean.setMap_Check(rs.getInt("Map_Check"));
				result.add(aeBean);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminExeBean[0]);
	}
	
	
	public AdminExeBean finalYoungList(AdminExeBean aeBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct Bby_Gender,Bby_Kind,Bby_Money,Bby_photo,Bby_Add,Bby_Check" );
			sql.append(	" from BABYPLUS " );				
			sql.append( " where Members_Id=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				aeBean.setBby_Gender(rs.getInt(1));
				aeBean.setBby_Kind(rs.getString(2));
				aeBean.setBby_Money(rs.getString(3));
				aeBean.setBby_Photo(rs.getString(4));
				aeBean.setBby_Add(rs.getString(5));
				aeBean.setMembers_Id(aeBean.getMem_Id());
				aeBean.setBby_Check(rs.getInt(6));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeBean;
	}
	
	
	public AdminExeBean finalPhoneList(AdminExeBean aeBean){
		conn = connector.getConnection();
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct Mem_Phone,Mem_Mobile,Mem_Nick,Mem_Address1,Mem_Address2" );
			sql.append(	" from MEMBERS " );				
			sql.append( " where ID =?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,aeBean.getMem_Id());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				aeBean.setMem_Mobile(rs.getString("Mem_Mobile"));
				aeBean.setMem_Phone(rs.getString("Mem_Phone"));
				aeBean.setMem_Nick(rs.getString("Mem_Nick"));
				aeBean.setMem_Address1(rs.getString("Mem_Address1"));
				aeBean.setMem_Address2(rs.getString("Mem_Address2"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return  aeBean;
	}
	
	
	public int finalVvalue(int id){
		conn = connector.getConnection();
		
		int num = 0;
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select Vvalue_Sum" );
			sql.append(	" from ABLILTYSUM  " );				
			sql.append( " where Members_Id=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,id);
			
			rs = pstmt.executeQuery();
			
			
			
			while(rs.next()){
				
				num = rs.getInt("Vvalue_Sum");
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return num;
	}

	public int finalDvalue(int id){
		conn = connector.getConnection();
		
		int num = 0;
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select Dvalue_Sum" );
			sql.append(	" from DAYCHECKSUM  " );				
			sql.append( " where Members_Id=?" );
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setInt(1,id);
			
			rs = pstmt.executeQuery();
			
			
			
			while(rs.next()){
				
				num = rs.getInt("Dvalue_Sum");
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return num;
	}

	
	public HashMap<Integer,String> firstMap(AdminExeBean aeBean,String city,int vsum,int dsum){
		conn = connector.getConnection();
		
		HashMap<Integer,String> result = new HashMap<Integer,String>();
		
		int num = 0;
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct a.Mem_Nick,a.Mem_Id" );
			sql.append(	" from ADMINCHECKO a,OLDPLUS b,ABLILTYSUM c,DAYCHECKSUM d" );				
			sql.append( " where b.Members_Id = c.Members_Id and b.Members_Id = d.Members_Id and a.Mem_Id = b.Members_Id");
			sql.append(	" and b.Old_Add REGEXP CONCAT(' 東京都  ' ,?,'*')");
			sql.append(	" and Mem_Check = 1 ");
			sql.append(	" and b.Old_Kind = ? ");
			sql.append(	" and b.Old_Money = ? ");
			sql.append(	" and b.Old_Gender = ? ");
			sql.append(	" and c.Vvalue_Sum = ? ");
			sql.append(	" and d.Dvalue_Sum = ? ");
	
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setString(1,city);
			pstmt.setString(2,aeBean.getBby_Kind());
			pstmt.setString(3,aeBean.getBby_Money());
			pstmt.setInt(4,aeBean.getBby_Gender());
			pstmt.setInt(5, vsum);
			pstmt.setInt(6, dsum);
			System.out.println(pstmt);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				aeBean = new AdminExeBean();
				aeBean.setMem_Nick(rs.getString("a.Mem_Nick"));
				aeBean.setMem_Id(rs.getInt("a.Mem_Id"));
				result.put(new Integer(rs.getInt("a.Mem_Id")),aeBean.getMem_Nick());
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result;
	}
	
	public ArrayList<AdminExeBean> secondMap(AdminExeBean aeBean,String city,int vsum,int dsum){
		conn = connector.getConnection();
		ArrayList<AdminExeBean> result = new ArrayList<AdminExeBean>();
		int num = 0;
		
		try{
			
			StringBuffer sql = new StringBuffer();
			
			sql.append( " select distinct a.Mem_Nick,a.Mem_Id" );
			sql.append(	" from ADMINCHECKO a,OLDPLUS b,ABLILTYSUM c,DAYCHECKSUM d" );				
			sql.append( " where b.Members_Id = c.Members_Id and b.Members_Id = d.Members_Id and a.Mem_Id = b.Members_Id");
			sql.append(	" and b.Old_Add REGEXP CONCAT(' 東京都  ' ,?,'*')");
			sql.append(	" and Mem_Check = 1 ");
			sql.append(	" and b.Old_Kind = ? ");
			sql.append(	" and b.Old_Money = ? ");
			sql.append(	" and b.Old_Gender = ? ");
			sql.append(	" and c.Vvalue_Sum & ? ");
			sql.append(	" and d.Dvalue_Sum & ? ");
			
			String sql1 = sql.toString();
			
			pstmt = conn.prepareStatement( sql1 );
			
			pstmt.setString(1,city);
			pstmt.setString(2,aeBean.getBby_Kind());
			pstmt.setString(3,aeBean.getBby_Money());
			pstmt.setInt(4,aeBean.getBby_Gender());
			pstmt.setInt(5, vsum);
			pstmt.setInt(6, dsum);
			
			System.out.println(pstmt);			
			rs = pstmt.executeQuery();
			while(rs.next()){
				aeBean = new AdminExeBean();
				aeBean.setMem_Nick(rs.getString("a.Mem_Nick"));
				aeBean.setMem_Id(rs.getInt("a.Mem_Id"));
				result.add(aeBean);
				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result;
	}
	
	public void mapping(int old,int young){
		
		conn = connector.getConnection();
		try{
				
				String sql = "insert into MAPPING (ID,Map_Old,Map_Young,Map_Check) values(null,?,?,1)";
				
				pstmt =  conn.prepareStatement( sql );
				pstmt.setInt(1,old);
				pstmt.setInt(2,young);
				pstmt.executeUpdate();
			
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
	}
	
	public int mappingCount(){
		
		conn = connector.getConnection();
		try{
				
				String sql = "select count(*) from MAPPING where Map_Check=2";
				
				pstmt =  conn.prepareStatement( sql );
				rs = pstmt.executeQuery();
				
				while(rs.next()){
					int count = rs.getInt("count(*)");
					return count;
				}
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				connector.releaseConnection(conn, pstmt, rs);
			}
			return 0;
		}
	
	
}
	
